//Consolidate all datasets
use "Data/cleaned_data/CountryGDP.dta", clear
merge 1:1 country year using "Data/cleaned_data/CountryExpenditure.dta"
drop _merge
merge 1:1 country year using "Data/cleaned_data/CountryRevenue.dta"
drop _merge
merge 1:1 country year using "Data/cleaned_data/CountryGrossDebt.dta"
drop _merge country_id
destring(year), replace
merge 1:1 country year using "Data/cleaned_data/CountryBondYieldLongTerm.dta"
drop _merge 
tostring year, replace
merge m:1 year using "Data/cleaned_data/euBondYieldLongTerm.dta"
drop _merge 
merge 1:1 country year using "Data/cleaned_data/CountryRatings.dta"
drop _merge 
merge m:1 country using "Data/cleaned_data/AssistanceAmount.dta"
drop _merge 
sort country year
destring year, replace force
drop countryId

//Dropping countries
	*drop the euroarea rows
drop if inlist(country, "Euro area (EA11-1999, EA12-2001, EA13-2007, EA15-2008, EA16-2009, EA17-2011, EA18-2014, EA19-2015, EA20-2023)", "Euro area - 12 countries (2001-2006)", "Euro area - 19 countries  (2015-2022)", "Euro area – 20 countries (from 2023)", "European Union - 27 countries (from 2020)")
drop if inlist(country, "Albania", "Bosnia and Herzegovina", "Iceland", "Kosovo", "Liechtenstein")
drop if inlist(country, "Montenegro", "North Macedonia", "Norway", "Serbia", "Switzerland", "Türkiye")
distinct country
	*earliest start date is 1999
	*cut all observations before year 1998
drop if year < 1998
	*cut all observations by countries' join date
drop if inlist(country, "Cyprus", "Czechia", "Estonia", "Hungary", "Latvia") & year < (2004-1)
drop if inlist(country, "Lithuania", "Malta", "Poland", "Slovakia", "Slovenia") & year < (2004-1)
drop if inlist(country, "Bulgaria", "Romania") & year < (2007-1)
drop if inlist(country, "Croatia") & year < (2014-1)
drop if inlist(country, "United Kingdom") & year > 2019
	*cut all non-Euro-using countries
drop if inlist(country, "Bulgaria", "Czechia", "Denmark", "Hungary", "Poland", "Romania", "Sweden", "United Kingdom")
	*certain countries didn't start using euro immediately after joining the EU
drop if inlist(country, "Greece") & year < (2001-1)
drop if inlist(country, "Solvenia") & year < (2007-1)
drop if inlist(country, "Cyprus", "Malta") & year < (2008-1)
drop if inlist(country, "Solvakia") & year < (2009-1)
drop if inlist(country, "Estonia") & year < (2011-1)
drop if inlist(country, "Latvia") & year < (2014-1)
drop if inlist(country, "Lithuania") & year < (2015-1)
drop if inlist(country, "Croatia") & year < (2023-1)
	*estonia wasn't in the long-term bond market until 2021
drop if inlist(country, "Estonia") & year < (2021)

//the year of financial assistance
gen assistanceYear =.
replace assistanceYear = 1 if country == "Ireland" & inlist(year, 2011, 2012, 2013)
replace assistanceYear = 1 if country == "Greece" & inlist(year, 2012, 2013, 2014, 2015, 2016, 2017, 2018)
replace assistanceYear = 1 if country == "Spain" & inlist(year, 2012, 2013)
replace assistanceYear = 1 if country == "Cyprus" & inlist(year, 2013)
replace assistanceYear = 1 if country == "Portugal" & inlist(year, 2011, 2012)
//assistanceAmount is turn the annual average of total financial assistance amount (because it's hard to determine the amount of assistance actualized in each year, I'm assuming a constant flow over the years) 
bysort country (year): egen aCounter = total(assistanceYear == 1)
rename assistanceAmount assistanceAmountTotal
gen assistanceAmount=assistanceAmountTotal/aCounter
replace assistanceAmount=0 if assistanceYear==.
drop aCounter

//financial assistance year isn't the same as bond market absence years
gen bondMarketAbsence =.
	*ireland never fully stopped bond issuance, but effectively lost access during the bailout program
replace bondMarketAbsence = 1 if country == "Ireland" & inlist(year, 2010, 2011)
replace bondMarketAbsence = 1 if country == "Greece" & inlist(year, 2010, 2011, 2012, 2013)
	*spain never stopped issuance
replace bondMarketAbsence = 1 if country == "Cyprus" & inlist(year, 2012, 2013)
replace bondMarketAbsence = 1 if country == "Portugal" & inlist(year, 2011, 2012)

//covid debt ceiling lift
gen covidDebtCeilingLift = 1 if inlist(year, 2020, 2021, 2022, 2023, 2024, 2025)

//in order for estimation to work, the default rate cannot be missing
//hence the missing values will be replaced with 0, assuming no estimations done by raters means they're perceived as perfectly default-free
replace defaultRateAvgAnnual = 0 if missing(defaultRateAvgAnnual)

//capping interest rate floor to 0 
	*22 country years with negative interest rate
count if sInterestRateAvgAnnual<0
	*certain countries have negative interest rate in years starting 2019, countries were discouraging savings and encouraging investment/activity through hugging the zero lower bound
*scatter sInterestRateAvgAnnual year if sInterestRateAvgAnnual<0
replace sInterestRateAvgAnnual =0 if sInterestRateAvgAnnual<0

save "Data/cleaned_data/Master.dta", replace

use "Data/cleaned_data/Master.dta", clear
//PEPP supranationals: gdp weight
keep country year gdp
keep if year >= 2020
drop if year == 2025
bysort year: gen gdpSum = sum(gdp)
bysort year: replace gdpSum = gdpSum[_N]
sort country year
gen gdpWeight = gdp/gdpSum
save "Data/cleaned_data/gdpWeight.dta", replace
*treating "supranationals" group
use "Data/cleaned_data/PEPPAmounttemp.dta", clear
destring(year), replace
drop if covidAssistanceAmount == 0
keep if country== "Supranationals"
drop country totalCurrentHoldings
merge 1:m year using "Data/cleaned_data/gdpWeight.dta"
drop gdp gdpSum _merge
sort country year
replace covidAssistanceAmount = covidAssistanceAmount*gdpWeight
drop gdpWeight
rename covidAssistanceAmount supranationalsAmount
tostring year, replace
merge 1:1 country year using "Data/cleaned_data/PEPPAmounttemp.dta"
drop if country == "Supranationals"
replace covidAssistanceAmount=0 if covidAssistanceAmount==.
replace covidAssistanceAmount = covidAssistanceAmount + supranationalsAmount
drop supranationalsAmount totalCurrentHoldings _merge
drop if year == "2025"
drop if covidAssistanceAmount==.
sort country year
save "Data/cleaned_data/PEPPAmount.dta", replace
*merging
destring(year), replace
merge 1:1 country year using "Data/cleaned_data/Master.dta"
drop _merge
sort country year
replace covidAssistanceAmount=0 if covidAssistanceAmount==.
save "Data/cleaned_data/Master.dta", replace